package com.ggkt.common.utils;

import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: ggkt
 * @Description: excel导入数据
 */
@Component
public class ImportExcelUtil {

    public static final String XLS = ".xls";
    public static final String XLSX = ".xlsx";

    public static <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz) throws IOException {
        return readExcelFileToDTO(file, clazz, 0);
    }

    public static <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz, Integer sheetId) throws IOException {
        //将文件转成workbook类型
        Workbook workbook = buildWorkbook(file);
        //第一个表
        return readSheetToDTO(workbook.getSheetAt(sheetId), clazz);
    }

    public static <T> List<T> readSheetToDTO(Sheet sheet, Class<T> clazz) {
        List<T> result = new ArrayList<>();
        List<Map<String, String>> sheetValue = changeSheetToMapList(sheet);
        for (Map<String, String> valueMap : sheetValue) {
            T dto = buildDTOByClass(clazz, valueMap);
            if (dto != null) {
                result.add(dto);
            }
        }
        return result;
    }

    //类型转换
    private static Workbook buildWorkbook(MultipartFile file) throws IOException {
        String filename = file.getOriginalFilename();
        if(filename != null){
            if (filename.endsWith(XLS)) {
                return new HSSFWorkbook(file.getInputStream());
            } else if (filename.endsWith(XLSX)) {
                return new XSSFWorkbook(file.getInputStream());
            }else {
                throw new IOException("上传的文件必须为xls或xlsx格式");
            }
        }else{
            throw new IOException("未获取到上传文件");
        }
    }

    private static List<Map<String, String>> changeSheetToMapList(Sheet sheet) {
        List<Map<String, String>> result = new ArrayList<>();
        int rowNumber = sheet.getPhysicalNumberOfRows();
        String[] titles = getSheetRowValues(sheet.getRow(0)); // 第一行作为表头
        for (int i = 1; i < rowNumber; i++) {
            String[] values = getSheetRowValues(sheet.getRow(i));
            Map<String, String> valueMap = new HashMap<>();
            for (int j = 0; j < titles.length; j++) {
                if(values.length > j){
                    valueMap.put(titles[j], values[j]);
                }else{
                    valueMap.put(titles[j], "");
                }
            }
            result.add(valueMap);
        }
        return result;
    }

    private static <T> T buildDTOByClass(Class<T> clazz, Map<String, String> valueMap) {
        try {
            T dto = clazz.newInstance();
            for (Field field : clazz.getDeclaredFields()) {
                ApiModelProperty desc = field.getAnnotation(ApiModelProperty.class);
                String value = valueMap.get(desc.value());
                if (value != null) {
                    Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
                    method.invoke(dto, value);
                }
            }

            return dto ;
        } catch (Exception e) {
            e.getStackTrace();
        }
        return null;
    }

    private static String getSetMethodName(String name) {
        String firstChar = name.substring(0, 1);
        return "set" + firstChar.toUpperCase() + name.substring(1);
    }

    private static String[] getSheetRowValues(Row row) {
        if (row == null) {
            return new String[]{};
        } else {
            int cellNumber = row.getLastCellNum();
            List<String> cellValueList = new ArrayList<>();
            for (int i = 0; i < cellNumber; i++) {
                cellValueList.add(getValueOnCell(row.getCell(i)));
            }
            return cellValueList.toArray(new String[0]);
        }
    }

    private static String getValueOnCell(Cell cell) {
        if (cell == null)
        {
            return "";
        }
        switch (cell.getCellType())
        {
            case NUMERIC:
                DecimalFormat df = new DecimalFormat("0");
                return df.format(cell.getNumericCellValue());

            case STRING:
                return cell.getStringCellValue();

            case FORMULA:
                return cell.getCellFormula();

            case BLANK:
                return "";

            case BOOLEAN:
                return cell.getBooleanCellValue() + "";

            case ERROR:
                return cell.getErrorCellValue() + "";
        }
        return "";
    }
}
